Querying A Pandas DataFrame

# Example Python program to query a value
# using the index
import pandas as pds

# Create a DataFrame
lattice = [(-1, -1, -1),
            (-1, 0, -1),
            (-1, -1, -10)];
df = pds.DataFrame(lattice);
print("DataFrame:");
print(df);

# Add index and column labels
df.index     = ["r1", "r2", "r3"];
df.columns     = ["c1", "c2", "c3"];

# Query the value from a location using the indices
row = 1;
col = 1;
val = df.iat[row, col];
print("Value at the indexes %d,%d:"%(row, col));
print(val);

# Query the value from a location using labels
row = "r3";
col = "c3";
val = df.at[row, col];
print("Value at the labels %s,%s:"%(row, col));
print(val);

DataFrame:
   0  1   2
0 -1 -1  -1
1 -1  0  -1
2 -1 -1 -10
Value at the indexes 1,1:
0
Value at the labels r3,c3:
-10

# Example Python program that queries
# a group of values using the indexes and labels
import pandas as pds

tokens = [("ab", "bc", "cd"),
          ("de", "ef", "fg"),
          ("gh", "hi", "ij")];
df = pds.DataFrame(data = tokens);

df.index     = ["r1", "r2", "r3"];
df.columns     = ["x", "y", "z"];
print("DataFrame:");
print(df);

# Query different groups of values using indices
r1 = 0;
r2 = 0;
c1 = 1;
c2 = 1;
print("Query results using indices [%d, %d], [%d, %d]:"%(r1, r2, c1, c2));
print(df.iloc[[r1,r2], [c1,c2]]);

r1 = 0;
r2 = 1;
c1 = 1;
c2 = 2;
print("Query results using indices [%d, %d], [%d, %d]:"%(r1, r2, c1, c2));
print(df.iloc[[0,1], [1,2]]);

# Query different groups of values using labels
r1 = "r1";
r2 = "r2";
c1 = "x";
c2 = "z";
print("Query results using labels [\"%s\", \"%s\"], [\"%s\", \"%s\"]:"%(r1, r2, c1, c2));
print(df.loc[["r1","r2"], ["x","z"]]);

DataFrame:
     x   y   z
r1  ab  bc  cd
r2  de  ef  fg
r3  gh  hi  ij
Query results using indices [0, 0], [1, 1]:
     y   y
r1  bc  bc
r1  bc  bc
Query results using indices [0, 1], [1, 2]:
     y   z
r1  bc  cd
r2  ef  fg
Query results using labels ["r1", "r2"], ["x", "z"]:
     x   z
r1  ab  cd
r2  de  fg

# Example Python program that pops a value
# from a DataFrame
import pandas as pds

# Create a pandas DataFrame
vals = [("I","II","III"),
        ("IV","V","VI"),
        ("VII","VIII","IX")];

df = pds.DataFrame(data=vals);
df.columns = ["c1", "c2", "c3"];
df.index = ["r1", "r2", "r3"];
print("DataFrame:");
print(df);

# Remove a column
popped = df.pop("c2");
print("Removed column:");
print(popped);
print(type(popped))

print("DataFrame after popping a column:");
print(df);

# Example Python program that queries
# a pandas DataFrame using a Boolean
# expression
import pandas as pds

fractions = [(1.1, 1.2, 1.3),
               (1.4, 1.5, 1.6),
               (1.7, 1.8, 1.9)];

# Create a DataFrame
df = pds.DataFrame(data=fractions,
                   columns=["c1", "c2", "c3"],
                   index = ["r1", "r2", "r3"]);    

print("DataFrame:");
print(df);

# Query the rows where a column value exceeds a threshold
threshold = 1.1;
queryResults = df.query("c1 > @threshold");
print("Query results:");
print(queryResults);

# Example python program that queries
# non-none rows of a DataFrame using DateTimeIndex
import pandas as pds

# Prices of commodities
prices = {"Commodity 1": [110.1, None, 109.6, 105.5, 104.2, 107.0],
          "Commodity 2": [250.1, 250.0, 245.1, None, 249.5, 252.5],
          "Commodity 3": [310.1, 320.0, 312.5, None, 315.2, 309.1]
         }

# Create a pandas DataFrame
df = pds.DataFrame(data = prices);
dateTimes = ['2020-08-05 12:31:10',
             '2020-08-05 12:32:11',
             '2020-08-05 12:33:12',
             '2020-08-05 12:34:13',
             '2020-08-05 12:35:14',
             '2020-08-05 12:35:15',
             ];

# Create an index of DateTime instances
dIndex = pds.DatetimeIndex(dateTimes);

# Attach the index to the DataFrame
df.index = dIndex;             
print(df);

# Query the rows that has non-none values between
# t0 and t4
df1 = df.asof(pds.DatetimeIndex(['2020-08-05 12:31:10',
                                    '2020-08-05 12:35:14']));
# Print the query results
print(df1);